package com.hbnu.dao;

import com.hbnu.pojo.Record;
import com.hbnu.pojo.User;
import com.hbnu.util.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDao {
    private Connection connection=null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public void insertRecord(String name, String stringBuilder, String record) {
        try {

            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into user_record values (?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, stringBuilder);
            preparedStatement.setTimestamp(3, new Timestamp(new Date().getTime()));
            preparedStatement.setString(4, record);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            System.out.println("添加战绩失败");
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public User findUserByusernamAndpassword(String name, String password) {
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from user_information where username = ? and password = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                User user = new User();
                user.setName(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setPhone(resultSet.getString("phonenumber"));
                user.setSegment(resultSet.getInt("segment"));
                user.setDisabled(resultSet.getInt("disabled"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public User findPasswordByPhone(String phoneNumber) {
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from user_information where phonenumber = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, phoneNumber);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                User user = new User();
                user.setName(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setPhone(resultSet.getString("phonenumber"));
                user.setSegment(resultSet.getInt("segment"));
                user.setDisabled(resultSet.getInt("disabled"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public boolean updatePasswordByPhone(String phoneNumber,String newPassword) {
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "update user_information set password = ? where phoneNumber = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newPassword);
            preparedStatement.setString(2, phoneNumber);
            int execute = preparedStatement.executeUpdate();
            connection.commit();
            if (execute == 0) {
                return false;
            }else {
                return true;
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            System.out.println("修改密码失败");
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean creatUser(String name, String password, String phoneNumber) {
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into user_information(username,password,phonenumber,segment,disabled) values (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            preparedStatement.setString(3, phoneNumber);
            preparedStatement.setInt(4, 1);
            preparedStatement.setInt(5, 0);
            int execute = preparedStatement.executeUpdate();
            connection.commit();
            if (execute == 0) {
                return false;
            }else {
                return true;
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            System.out.println("创建用户失败");
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public int deleteUser(String dName) {
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "delete from user_information where username = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, dName);
            int execute = preparedStatement.executeUpdate();
            connection.commit();
            return execute;
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            System.out.println("删除用户失败");
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public User findUserByusername(String name) {
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from user_information where username = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                User user = new User();
                user.setName(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setPhone(resultSet.getString("phonenumber"));
                user.setSegment(resultSet.getInt("segment"));
                user.setDisabled(resultSet.getInt("disabled"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Record> findRecordByName(String name) {
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from user_record where username = ? order by date desc limit 20";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            List<Record> records =new ArrayList<>();
            while (resultSet.next()){
                Record record = new Record();
                record.setUser(resultSet.getString("username"));
                record.setOutcome(resultSet.getString("outcome"));
                record.setDate(resultSet.getTimestamp("date"));
                record.setPlayAll(resultSet.getString("playall"));
                records.add(record);
            }
            return records;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public void updateSegmentByName(String name, int num) {
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "update user_information set segment = segment + (?) where username = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, num);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            System.out.println("段位修改失败");
        }finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
